﻿Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlClient

Public Class frmConnect
    Private servers As SqlDataSourceEnumerator
    Private tableServers As DataTable
    Private server As String

    Public Sub New()
        InitializeComponent()
        servers = SqlDataSourceEnumerator.Instance
        tableServers = New DataTable()
    End Sub
    
    Private Sub cmbServerName_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbServerName.Click
        ' Check if datatable is empty
        If tableServers.Rows.Count = 0 Then

            ' Get a datatable with info about SQL Server 2000 and 2005 instances
            tableServers = servers.GetDataSources()

            ' List that will be combobox’s datasource   
            Dim listServers As List(Of String) = New List(Of String)

            ' For each element in the datatable add a new element in the list
            For Each rowServer As DataRow In tableServers.Rows

                ' SQL Server instance could have instace name or only server name,
                ' check this for show the name
                If String.IsNullOrEmpty(rowServer("InstanceName").ToString()) Then
                    listServers.Add(rowServer("ServerName").ToString())
                Else
                    listServers.Add(rowServer("ServerName") & "\\" & rowServer("InstanceName"))
                End If

            Next

            'Set servers list to combobox’s datasource
            Me.cmbServerName.DataSource = listServers
        End If
    End Sub

    Private Sub cmbDuLieu_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbDuLieu.Click
        Dim listDataBases As List(Of String) = New List(Of String)
        Dim connectString As String
        Dim selectSQL As String

        ' Check if user was selected a server to connect
        If Me.cmbServerName.Text = "" Then
            MsgBox("Must select a server")
            Return
        End If

        server = Me.cmbServerName.Text

        'Set connection string with selected server and integrated security
        connectString = "Data Source=" & server & " ;Integrated Security=True;Initial Catalog=master"


        Using con As New SqlConnection(connectString)

            ' Open connection
            con.Open()

            'Get databases names in server in a datareader
            selectSQL = "select name from sys.databases;"

            Dim com As SqlCommand = New SqlCommand(selectSQL, con)
            Dim dr As SqlDataReader = com.ExecuteReader()


            While (dr.Read())
                listDataBases.Add(dr(0).ToString())
            End While

            'Set databases list as combobox’s datasource
            Me.cmbDuLieu.DataSource = listDataBases

        End Using

    End Sub
End Class